<?php
/**
+--------------------------------------------------------------
 * User: maqun
+--------------------------------------------------------------
 * Date: 2015/11/23
+--------------------------------------------------------------
 * Time: 22:00
+--------------------------------------------------------------
 * 题目数据统计
 */
class SolutionModel extends BaseModel{
    protected $tableName = 'solution'; //数据库表名

    /**
     * @param string $start 统计数据开始时间
     * @param string $end   统计数据结束时间
     * @param string $user_id 当用户id不为空时获取某个用户的ac和提交记录，为空时获取全部用户数据
     * @return array 返回ac和提交数据，用于统计图，首页统计图和个人中心的统计图
     */
    public  function getInfo($start='',$end='',$user_id=''){
        $where = " where 1 ";
        if(!empty($start) && !empty($end)){
            $start = strtotime($start);
            $end = strtotime($end);
            $where .= " and UNIX_TIMESTAMP(in_date) between {$start} and {$end} ";
        }
        if(!empty($user_id)){
            $where .= " and user_id='{$user_id}' ";
        }
        $return_info = array('status'=>'','info'=>'');
        $sql_sub  ="SELECT UNIX_TIMESTAMP(date(in_date)) md,count(1) c FROM solution {$where} group by md order by md asc ";
        $sql_ac   ="SELECT UNIX_TIMESTAMP(date(in_date)) md,count(1) c FROM solution {$where} and result=4 group by md order by md asc ";
        $submit   = $this->query($sql_sub);
        $ac       = $this->query($sql_ac);
        $sub_temp = array();
        $temp_cate=array();
        $i = 0;
        foreach($submit as $k=>$v){
            if(0===$i){
                $temp_cate[] = date("M.d ",$v['md']);
            }
            $sub_temp[]  = intval($v['c']);
        }
        $ac_temp = array();
        foreach($ac as $k=>$v){
            $ac_temp[] = intval($v['c']);
        }
        if(!empty($sub_temp) && !empty($ac_temp)){
            $return_info['status'] = true;
            $return_info['info']['cate_sub'] = $temp_cate;
            $return_info['info']['sub'] = $sub_temp;
            $return_info['info']['ac'] = $ac_temp;
            $return_info['info']['count'] = count($temp_cate);
        }

        return $return_info;
    }

    /**
     * @param $user_id 用户id
     * @return array
     * 通过用户id返回用户做题统计信息
     */
    public function getUserSolution($user_id){
        $return_info = array();

        //做题信息统计
        $solution_info = array();
        $resutl_type = C('result');
        $sql = "select `result` as `name`,count(`result`) as `value` from `solution` where user_id='{$user_id}' and `result` >=4 group by `result` ";
        $res = $this->query($sql);
        if(!empty($res)){
            foreach($res as $k=>$v){
                $temp['name'] = $resutl_type[$v['name']]['name'];
                $temp['num']  = $v['value'];
                $return_info['solution_info'][] = $temp;
            }
        }
        //解决过得问题id
        $return_info['problem_ids'] = $this->field('problem_id')->where("user_id='{$user_id}' and result =4")->group('problem_id')->select();

        //获取排名
        //更新用户信息表
        $ac_num  = count($return_info['problem_ids']);
        $sub_num = $this->query("select count(*) as `num` from `solution` where `user_id`='{$user_id}'");
        $sub_num = !empty($sub_num) ? intval($sub_num[0]['num']) : 0;
        $res = D('Users')->where("user_id='{$user_id}'")->setField(array('solved'=>$ac_num,'submit'=>$sub_num));

        $rangking = $this->query("select count(*) as `num` from `users` where `solved`>{$ac_num}");
        $return_info['rangking'] = !empty($rangking)? intval($rangking[0]['num'])+1 : 1;
        $return_info['ac_num'] = $ac_num;
        $return_info['sub_num'] = $sub_num;

        return $return_info;
    }
    /**
     * @param $start
     * @param $end
     * @return array|mixed
     * 获取$start---$end时间段的排名信息
     * 首页排名top10
     */
    public function getTopRank($start='',$end=''){
        $day = date('d',time());
        $month = date('m',time());
        $year = date('Y',time());
        $week = date('N',time());
        $stime = empty($start)?date('Y-m-d H:i:s',strtotime($year.'-'.$month.'-'.($day-$week%8+1))):$start;
        $etime = empty($end)? date('Y-m-d H:i:s',time()):$end;

        $where  = " where 1  ";
        if(!empty($stime)){
            $where .= " and unix_timestamp(in_date) > ".strtotime($stime)." ";
        }
        if(empty($etime)){
            $where .= " and unix_timestamp(in_date) < ".strtotime($etime)." ";
        }
        $table1 = " (select count(distinct(problem_id)) as solved,user_id from solution {$where} and result=4 group by user_id) ";
        $table2 = " (select count(distinct(problem_id)) as submit,user_id from solution {$where} group by user_id) ";

        $sql   = "select  a.user_id,a.nick,a.reg_time,b.solved,c.submit from users as a right join {$table1} as b on a.user_id=b.user_id left join {$table2} as c on a.user_id=c.user_id where a.submit >0 order by b.solved DESC,c.submit DESC,a.reg_time limit 10";
        $list  = $this->query($sql);
        return $list;
    }

    public function getGradeRank($start,$end){
        $return_info =array();
        for($i=2011;$i<=date("Y",time());$i++){
            $return_info['grade'][] = $i;
        }
        $user_id = implode(",",$return_info['grade']);

        $sub_sql = "SELECT SUBSTR(user_id,1,4) as grade,count(1) as total from solution WHERE SUBSTR(user_id,1,4) in({$user_id}) and UNIX_TIMESTAMP(in_date) BETWEEN ".strtotime($start)." and ".strtotime($end)." GROUP BY grade ";
        $ac_sql =  "SELECT SUBSTR(user_id,1,4) as grade,count(1) as total from solution WHERE SUBSTR(user_id,1,4) in({$user_id}) and result=4 and UNIX_TIMESTAMP(in_date) BETWEEN ".strtotime($start)." and ".strtotime($end)." GROUP BY grade ";
        $return_info['sub'] = $this->query($sub_sql);
        $return_info['ac']  = $this->query($ac_sql);
        return $return_info;
    }
}